<?php
set_include_path('system'.PATH_SEPARATOR.'templates');

require_once('Template.php');
require_once('RedirectBrowserException.php');
require_once('DatabaseUtility.php');

$tmpl = new Template();

openDatabase();

$query = "SELECT COUNT(*) as count FROM protein;";
$result = runQuery($query);
$procount = number_format($result[0]['count']);

$query = "SELECT COUNT(*) as count FROM site WHERE site.ptm='p';";
$result = runQuery($query);
$sitecount = number_format($result[0]['count']);

$query = "SELECT version FROM p3dbInfo";
$result = runQuery($query);
$version = $result[0]['version'];

$query ="SELECT COUNT(*) AS count FROM dataSource ORDER BY pubmed DESC;";
$result = runQuery($query);
$numref = $result[0]['count'];

$sitecountorg = array();
$query = "SELECT COUNT(*) as count,organism FROM site,protein WHERE site.protein=protein.id AND site.ptm='p' GROUP BY organism;";
$result = runQuery($query);
foreach($result as $row) {
	$sitecountorg[$row['organism']] = number_format($row['count']);
}

$procountorg = array();
$query = "SELECT COUNT(*) as count,organism FROM protein GROUP BY organism;";
$result = runQuery($query);
foreach($result as $row) {
	$procountorg[$row['organism']] = number_format($row['count']);
}

$orgs = array();
$query ="SELECT label,latin FROM organism ORDER BY latin;";
$result = runQuery($query);
foreach($result as $row) {
	$orgs[$row['label']] = $row['latin'];
}

$refs = array();
$query ="SELECT id,link,reference FROM dataSource ORDER BY pubmed DESC;";
$result = runQuery($query);
foreach($result as $row) {
	$id = $row['id'];
	$link = $row['link'];
	if (empty($link)) {
			$refs[$id] = $row['reference'];
	} else {
			$refs[$id] = "<a href=\"{$link}\" target=\"_blank\">{$row['reference']}</a>";
	}
}

$procountorgref =  array();
$query = "SELECT COUNT(*) as count,organism,dataSource FROM protein,proteinDataSourceRelation ".
		 "WHERE protein.id=proteinDataSourceRelation.protein ".
		 "GROUP BY organism, dataSource;";
$result = runQuery($query);
foreach($result as $row) {
	$procountorgref[$row['organism']][$row['dataSource']] = number_format($row['count']);
}

$sitecountorgref = array();
$query = "SELECT COUNT(*) as count,organism,dataSource FROM site,siteDataSourceRelation,protein ".
		 "WHERE site.id=siteDataSourceRelation.site AND site.protein=protein.id ".
		 "GROUP BY organism, dataSource;";
$result = runQuery($query);
foreach($result as $row) {
	$sitecountorgref[$row['organism']][$row['dataSource']] = number_format($row['count']);
}

$procountref = array();
$query = "SELECT COUNT(*) as count,dataSource FROM protein,proteinDataSourceRelation ".
		 "WHERE protein.id=proteinDataSourceRelation.protein ".
		 "GROUP BY dataSource;";
$result = runQuery($query);
foreach($result as $row) {
	$procountref[$row['dataSource']] = number_format($row['count']);
}

$sitecountref = array();
$query = "SELECT COUNT(*) as count,dataSource FROM site,siteDataSourceRelation,protein ".
		 "WHERE site.id=siteDataSourceRelation.site AND site.protein=protein.id AND site.ptm='p' ".
		 "GROUP BY dataSource;";
$result = runQuery($query);
foreach($result as $row) {
	$sitecountref[$row['dataSource']] = number_format($row['count']);
}

$tmpl->procountorgref = $procountorgref;
$tmpl->sitecountorgref = $sitecountorgref;
$tmpl->sitecountorg = $sitecountorg;
$tmpl->procountorg = $procountorg;
$tmpl->procountref = $procountref;
$tmpl->sitecountref = $sitecountref;
$tmpl->procount = $procount;
$tmpl->sitecount = $sitecount;
$tmpl->version = $version;
$tmpl->orgs = $orgs;
$tmpl->refs = $refs;
$tmpl->numref = $numref;

closeDatabase();

$tmpl->current = 'data sets';
$tmpl->pageContent = $tmpl->build('datasets.tmpl');

print $tmpl->build('page.tmpl');
?>